package net.sky.oa.dao.system.impl;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.sky.oa.dao.SkyBaseDaoImpl;
import net.sky.oa.dao.system.SysEmployeeDao;
import net.sky.oa.model.SysEmployee;
import net.sky.oa.util.Lunar;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.htsoft.core.web.paging.PagingBean;

@SuppressWarnings("unchecked")
@Repository
public class SysEmployeeDaoImpl extends SkyBaseDaoImpl<SysEmployee> implements SysEmployeeDao {
	
	public SysEmployeeDaoImpl() {
		super.setPersistType(SysEmployee.class);
	}

	public List<SysEmployee> findAllEmployee() {
		String hql = "from SysEmployee s";
		return findByHql(hql);
	}

	public SysEmployee getByUsername(String username) {
		return (SysEmployee)super.findUnique("from SysEmployee e where e.username=?", new String[]{username});
	}
	
	public List<SysEmployee> findBySch(Long sch) {
		String hql = "from SysEmployee d where d.sysschool.id=?";
		Object[] params = { sch };
		return findByHql(hql, params);
	}
	public List<SysEmployee> findByDept(Long dept) {
		String hql = "from SysEmployee d where d.dept.id=?";
		Object[] params = { dept };
		return findByHql(hql, params);
	}
	public List<SysEmployee> findByPost(Long post) {
		String hql = "from SysEmployee d where d.post.id=?";
	    Object[] params = { post };
	    return findByHql(hql, params);
	}
	public List<SysEmployee> getNewEmpl(PagingBean pb){
		ArrayList params = new ArrayList();
		StringBuffer hql = new StringBuffer("select new SysEmployee(id,name,sex,mobile,jointime) " +
				"from SysEmployee n where n.status ="+1+" and n.id<>"+1);
		if(null==pb){
			hql.append(" order by n.jointime desc");
			return findByHql(hql.toString(), params.toArray());
		}else{
			hql.append(" and n.jointime is not null order by n.jointime desc");
			return findByHql(hql.toString(), params.toArray(), pb);
		}
	}
	@SuppressWarnings("static-access")
	public List<SysEmployee> getBirthday(){
		
		try {
			List<SysEmployee> list=new ArrayList<SysEmployee>();
			Lunar lunar=new Lunar();
			final String today=lunar.getDate();
			final int Ldays = lunar.getNonthDays();
			SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
			final Date day = simpleDateFormat.parse(today);
			
			final Date day2 = simpleDateFormat.parse("2012-01-01");
			list=(List)getHibernateTemplate().execute(new HibernateCallback() {
				public List doInHibernate(final Session session)
						throws HibernateException, SQLException {
					DateFormat dateFormat = new SimpleDateFormat("MM-dd");
					String date = dateFormat.format(new Date());
					
					String[] mon_day = date.split("-");
					String mon = mon_day[0];
					int days = Integer.valueOf(mon_day[1]);
					
					String[] L_monDay = today.split("-");
					String L_mon = L_monDay[1];
					int L_days = Integer.valueOf(L_monDay[2]);
					
					StringBuffer sb = new StringBuffer();
					List list1 = new ArrayList();
					if("12".equals(L_mon)){
						sb.append("select * from sys_employee s where (s.status =1 and s.calendartype=1 and to_char(s.birthday,'MM-dd') between to_char(sysdate,'MM-dd') and to_char(sysdate+30,'MM-dd')) " +
						"or (s.status=1 and s.calendartype=2 and to_char(s.birthday,'MM-dd') between to_char(?,'MM-dd') and to_char(?+?,'MM-dd'))"+
						"or (s.status=1 and s.calendartype=2 and to_char(s.birthday,'MM-dd') between to_char(?,'MM-dd') and to_char(?+?,'MM-dd')) order by (to_char(s.birthday,'MM-dd')) asc" );
						Query query = session.createSQLQuery(sb.toString()).addEntity(SysEmployee.class);
						query.setParameter(0, day);
						query.setParameter(1, day);
						query.setParameter(2, (Ldays-L_days));
						query.setParameter(3, day2);
						query.setParameter(4, day2);
						query.setParameter(5, L_days);
						list1=query.list();
					}else if("12".equals(mon)){
						sb.append("select * from sys_employee s where (s.status =1 and s.calendartype=1 and to_char(s.birthday,'MM-dd') between to_char(sysdate,'MM-dd') and to_char(sysdate+?,'MM-dd')) " +
								"or (s.status=1 and s.calendartype=1 and to_char(s.birthday,'MM-dd') between to_char(?,'MM-dd') and to_char(?+?,'MM-dd')) "+
						"or (s.status=1 and s.calendartype=2 and to_char(s.birthday,'MM-dd') between to_char(?,'MM-dd') and to_char(?+30,'MM-dd')) order by (to_char(s.birthday,'MM-dd')) asc" );
						Query query = session.createSQLQuery(sb.toString()).addEntity(SysEmployee.class);
						query.setParameter(0, (31-days));
						query.setParameter(1, day2);
						query.setParameter(2, day2);
						query.setParameter(3, days);
						query.setParameter(4, day);
						query.setParameter(5, day);
						list1=query.list();
					}else{
						sb.append("select * from sys_employee s where (s.status =1 and s.calendartype=1 and to_char(s.birthday,'MM-dd') between to_char(sysdate,'MM-dd') and to_char(sysdate+30,'MM-dd')) " +
						"or (s.status=1 and s.calendartype=2 and to_char(s.birthday,'MM-dd') between to_char(?,'MM-dd') and to_char(?+30,'MM-dd')) order by (to_char(s.birthday,'MM-dd')) asc" );
						Query query = session.createSQLQuery(sb.toString()).addEntity(SysEmployee.class);
						query.setParameter(0, day);
						query.setParameter(1, day);
						list1=query.list();
					}
					return list1;
				}
			});
			return list;
			
		} catch (ParseException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<Long> getSuper(final Long userId){
		List<Long> list=new ArrayList<Long>();
		List<SysEmployee> sysEmployees=new ArrayList<SysEmployee>();
		
		sysEmployees=(List<SysEmployee>)getHibernateTemplate().execute(new HibernateCallback() {
			public List<SysEmployee> doInHibernate(final Session session)
					throws HibernateException, SQLException {
					String sql="select * from sys_employee sm where  sm.name=(select sd.principal from sys_employee s,sys_dept sd where s.dept_id=sd.id and s.id=?)";
//					String sql="select * from sys_employee sm where  sm.name in(select sd.principal from sys_dept sd start with sd.id=(select ss.dept_id from sys_employee ss where ss.id=?) connect by prior sd.parent_id=sd.id)";
					Query query=session.createSQLQuery(sql.toString()).addEntity(SysEmployee.class);
					query.setParameter(0, userId);
					return query.list();
				}
			});
		if(sysEmployees.size()>0){
			for(SysEmployee s:sysEmployees){
				list.add(s.getId());
			}
		}
		return list;
	}
	//
	public List<SysEmployee> findByMobile(String mobile){
		String hql = "select new SysEmployee(s.id,s.name) from SysEmployee s where s.mobile='"+mobile+"'";
		return findByHql(hql);
		 
	}
}